clear all

// SET MACROS
global Input  "Y:/limited/Michigan_CTE/funding_change/data_raw"
global Output "Y:/limited/Michigan_CTE/funding_change/data_derived"

//PREP MERGE
use  cohort9 using "Y:/limited/Michigan_CTE/funding_change/data_final/compiled_student_analysis_data.dta", clear
gen all = 1
gcollapse (sum) all, by(cohort)
tempfile temp1
save `temp1'

local vars dcode year district_* hs_students cte_millage_pp 
use `vars' using "Y:/limited/Michigan_CTE/funding_change/data_final/dcode_funding.dta", clear
gen district_city = district_urbanicity==1
gen district_suburb = district_urbanicity==2
gen district_town = district_urbanicity==3
gen district_rural = district_urbanicity==4
gen district_urb_mflag = district_urbanicity==.


drop district_urbanicity district_most_poor district_poor_quartile
tempfile temp2
save `temp2'

local vars  isdcode  year district_* hs_students cte_millage_pp  
use `vars' using "Y:/limited/Michigan_CTE/funding_change/data_final/dcode_funding.dta", clear
preserve
gen district_city = district_urbanicity==1
gen district_suburb = district_urbanicity==2
gen district_town = district_urbanicity==3
gen district_rural = district_urbanicity==4
gen district_urb_mflag = district_urbanicity==.

drop district_urbanicity district_most_poor district_poor_quartile
gcollapse (median) district_city district cte_millage_pp hs_students [fw = hs_students], by(isdcode year)
tempfile temp3
save `temp3'
restore

gcollapse (sum)  hs_students , by(isdcode year)
merge 1:1 isdcode year using `temp3', nogen
ren isdcode dcode

tempfile 
save `temp3', replace





// LOAD DATA
local vars ric year grade cipcode adv_p4 total_segments segment13 total_mins assess* psn fano cepd
use `vars' using ${Input}/cte_program_enrollment.dta, clear

* keep students in sample
rename ric student
merge m:1 student using "Y:/limited/Michigan_CTE/funding_change/data_final/compiled_student_analysis_data.dta",  ///
	keep(3) keepusing(cohort9) nogen
	
drop if year > (cohort9 + 3)  	
drop if year < (cohort9)  		
	
	
*Cooking gets relabeled
replace cipcode = "12.0500" if cipcode == "12.9999"

* Family and consumer science is not federally recognized and is new in 2019
drop if cipcode=="19.0000"
	
//Segments
replace total_segments = 13 if total_segments==12 & inrange(segment13,2,4)

//Courses
merge m:1 student psn using ${Output}/course_count.dta, keep(1 3) nogen
recode course_count total_semesters (.=2)
replace course_count = 6 if course_count>6
ren course_count psn_course_count

// Completion status
bys student psn: gegen psn_completion_status = max(adv_p4)
bys student psn: gegen psn_completion_grade = min(cond(adv_p4==3,grade,.))
bys student psn: gegen psn_segments = max(total_segments)



***
* PSN COHORT
***
keep student cohort9 psn* cipcode fano cepd
*correct one weird psn
replace  cepd=10 if cepd==14 & psn == 16376 

* Collapse Key info
gduplicates drop
gen any = 1
gen comp = psn_completion_status==3
gen segQ = psn_segments==13
gen comp_course = psn_course_count if psn_completion_status==3
gcollapse (mean) psn_segments psn_course_count psn_completion_grade (sum) any comp segQ (min) comp_course, by(cohort9 cipcode fano psn cepd)



* Merge in Student Denominators
merge m:1 cohort9 using `temp1', nogen
gen part_rate = any/all
gen comp_rate = comp/all
gen Q_rate = segQ/all

* Merge in District Characteristics
ren (fano cohort9) (dcode year)
replace year = year +3
merge m:1 dcode year using `temp2', keep(1 3) nogen
merge m:1 dcode year using `temp3', update  keep(1 3 4) nogen
mdesc


* Merge in Funds Generated
merge 1:1 psn year using "Y:/limited/Michigan_CTE/funding_change/data_final/psn_funding.dta", keep(1 3) keepusing(funds_state60 funds_cepd40 funds_total)

***
* Label
***
drop all

label var year 			    "9th Grade Cohort Year"
label var cipcode 
label var dcode 			"District (or ISD)"
label var psn_segments 		"Average Number of Segments in PSN"
label var psn_course_count 	"Average Number of Courses taken in PSN"

label var any 				"Number of Students in PSN"
label var comp				"Number of Completers in PSN" 
label var segQ				"Number of Segment Q Completers in PSN" 
label var comp_course 		"Number of Students in PSN"
label var part_rate			"Unconditional Participation Rate in PSN" 
label var comp_rate			"Unconditional Complietion in PSN"
label var  Q_rate 			"Unconditional Segment Q Rate  in PSN"

label var district_cte_any 			"Percent CTE participants  in district (2012-2014)--or isd average"
label var district_cte_comp 		"Percent CTE completers in district (2012-2014)--or isd average"

label var cte_millage_pp  	"Per Pupil Millage Revenue in District (Average in ISD)--or isd average"
label var hs_students  		"Total Students in District (Average in ISD)"


save "Y:/limited/Michigan_CTE/funding_change/data_final/psn_analysis.dta", replace
			
		